package Dm

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strings"
)

func (or TableInfoMeta) ViewExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var (
		f1    any
		event = "[oracleViewExist]"
	)
	for _, vv := range []string{
		"ALL_VIEWS", "DBA_VIEWS",
		"ALL_MVIEWS", "DBA_MVIEWS",
	} {
		switch vv {
		case "USER_VIEWS", "ALL_VIEWS", "DBA_VIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select count(*) as \"sum\" from %v where 1=1 and OWNER = '%v' and VIEW_NAME = '%v'", vv, s.TableInfo.Schema, s.TableInfo.Table)
		case "USER_MVIEWS", "ALL_MVIEWS", "DBA_MVIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select count(*) as \"sum\" from %v where 1=1 and OWNER = '%v' and MVIEW_NAME = '%v'", vv, s.TableInfo.Schema, s.TableInfo.Table)
		}
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil || len(f1.(map[string]any)) == 0 {
			continue
		}
		if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
			result.Result = true
		}
		break
	}
	return
}

func (or TableInfoMeta) ViewName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var (
		f1    any
		event = "[oracleViewName]"
	)
	for _, vv := range []string{"ALL_VIEWS", "DBA_VIEWS", "ALL_MVIEWS", "DBA_MVIEWS"} {
		switch vv {
		case "USER_VIEWS", "ALL_VIEWS", "DBA_VIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select VIEW_NAME from %v where 1=1 and OWNER = '%v' %v", vv, s.TableInfo.Schema, func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" AND VIEW_NAME like '%%%v%%'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" AND VIEW_NAME = '%v'", s.TableInfo.Table)
				}
			}())
		case "USER_MVIEWS", "ALL_MVIEWS", "DBA_MVIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select MVIEW_NAME from %v where 1=1 and OWNER = '%v' %v", vv, s.TableInfo.Schema, func() string {
				switch {
				case s.TableInfo.Table == "*":
					return ""
				case strings.Contains(s.TableInfo.Table, "%"):
					return fmt.Sprintf(" AND MVIEW_NAME like '%%%v%%'", s.TableInfo.Table)
				default:
					return fmt.Sprintf(" AND MVIEW_NAME = '%v'", s.TableInfo.Table)
				}
			}())
		}
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.StringGarth,
		}); err != nil || f1 == nil || len(f1.([]string)) == 0 {
			continue
		}
		result.Result = append(result.Result, f1.([]string)...)
		break
	}
	return
}

func getNormalViewMeta(s global.SchemaObjectInput) (result global.ViewResultDefinition, err error) {
	var (
		f1    any
		event = "[oracleGetNormalViewMeta]"
	)
	for _, vv := range []string{
		//"USER_VIEWS",
		"ALL_VIEWS",
		"DBA_VIEWS",
	} {
		switch vv {
		case "USER_VIEWS", "ALL_VIEWS", "DBA_VIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select OWNER as \"schema\",VIEW_NAME as \"viewName\",TEXT as \"createView\" "+
				"from %v "+
				"WHERE 1 = 1 and "+
				"OWNER='%v' AND VIEW_NAME = '%v'", vv, s.Schema, s.Table)
		}
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil || len(f1.(map[string]any)) == 0 {
			continue
		}
		if err = global.MapSubToStructSubJsonName(f1.(map[string]any), &result); err != nil {
			return
		}
		result.Type = "view"
		break
	}
	return
}

func getMaterializedViewMeta(s global.SchemaObjectInput) (result global.ViewResultDefinition, err error) {
	var (
		f1    any
		event = "[oracleGetMaterializedViewMeta]"
	)
	for _, vv := range []string{
		//"USER_MVIEWS",
		"ALL_MVIEWS",
		"DBA_MVIEWS",
	} {
		switch vv {
		case "USER_MVIEWS", "ALL_MVIEWS", "DBA_MVIEWS":
			result.BaseResult.Sql = fmt.Sprintf("select OWNER as \"schema\",MVIEW_NAME as \"viewName\",QUERY as \"createView\" "+
				"from %v "+
				"WHERE 1 = 1 and "+
				"OWNER='%v' AND MVIEW_NAME = '%v'", vv, s.Schema, s.Table)
		}
		if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
			Ss:           s,
			Event:        event,
			SelectSqlStr: result.BaseResult.Sql,
			Expect:       ETL.Map,
		}); err != nil || f1 == nil || len(f1.(map[string]any)) == 0 {
			continue
		}
		if err = global.MapSubToStructSubJsonName(f1.(map[string]any), &result); err != nil {
			return
		}
		result.Type = "materialized"
		break
	}
	return
}
func viewPrintInfo(p global.ViewResultDefinition) (res string) {
	return fmt.Sprintf("CREATE VIEW \"%v\" AS \n"+
		"%v;", p.Name, p.Action)
}
func viewBodyPrepare(s global.SchemaObjectInput, p global.ViewResultDefinition) global.ViewDefinitionConvertS {
	//spr.Parse(strings.ToUpper(p.Action))
	//fmt.Println("----oo")
	//spr.Parse(strings.ToUpper(fmt.Sprintf("SELECT employee_id, salary \n    FROM employees \n    WHERE department_id IN (1000) and id >10 ;")))
	//spr.Parse(strings.ToUpper(fmt.Sprintf("SELECT AVG(employee_id) AS avg_employee_id,id AS id1,count(*) as sum,count(employee_id),COUNT(DISTINCT manager_id) \"Managers\" \nFROM (\n    SELECT employee_id, salary \n    FROM employees \n    WHERE department_id IN (\n        SELECT department_id \n        FROM departments \n        WHERE location_id = 1000\n    )\n)  dept_employees\nWHERE salary > 50000")))
	//fmt.Println("----jj")
	//spr.Parse(strings.ToUpper(fmt.Sprintf("WITH dept_salary AS (\n    SELECT department_id, AVG(salary) AS avg_salary\n    FROM employees\n    GROUP BY department_id\n)\nSELECT DISTINCT employee_id, salary\nFROM employees\nWHERE department_id IN (SELECT department_id FROM dept_salary WHERE avg_salary > 50000)\nGROUP BY department_id\nORDER BY salary;")))
	return global.ViewDefinitionConvertS{
		Schema:      s.Schema,
		Name:        s.Table,
		Description: p.Action,
		PrintInfo:   viewPrintInfo(p),
		BodyText:    p.Action,
	}
}
func convertView(s global.SchemaObjectInput, p global.ViewResultDefinition) (res global.ViewDefinitionConvertS, err error) {
	//var q global.JobInterval
	switch p.Type {
	case "view":
		res = viewBodyPrepare(s, p)
	case "materialized":
		//return dbmsJobConvert(s, p)
	default:
		return
	}
	return
}

func (or SchemaObjectMeta) View(s global.SchemaObjectInput) (res global.SchemaObjectReturnResult, err error) {
	var (
		definition global.ViewResultDefinition
		convert    global.ViewDefinitionConvertS
	)
	if definition, err = getNormalViewMeta(s); err != nil || len(definition.Name) == 0 {
		if definition, err = getMaterializedViewMeta(s); err != nil || len(definition.Name) == 0 {
			return
		}
	}
	if convert, err = convertView(s, definition); err != nil {
		return
	}
	definition.PrintInfo = convert.PrintInfo
	return global.SchemaObjectReturnResult{
		ViewResult: global.ViewObjectResult{
			Definition: definition,
			Convert:    convert,
		},
	}, err
}
